package io.renren.modules.com.service.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.xiaoleilu.hutool.date.DateUtil;
import com.xiaoleilu.hutool.util.CollectionUtil;
import com.xiaoleilu.hutool.util.NumberUtil;
import com.xiaoleilu.hutool.util.StrUtil;

import io.renren.common.exception.RRException;
import io.renren.common.utils.Constant;
import io.renren.common.utils.DateUtils;
import io.renren.common.utils.NumberUtils;
import io.renren.common.utils.R;
import io.renren.modules.com.dao.ComAssetDao;
import io.renren.modules.com.dao.ComContractAssetDao;
import io.renren.modules.com.dao.ComContractDao;
import io.renren.modules.com.dao.ComContractImageDao;
import io.renren.modules.com.dao.ComImageDao;
import io.renren.modules.com.entity.ComAssetEntity;
import io.renren.modules.com.entity.ComContractAssetEntity;
import io.renren.modules.com.entity.ComContractEntity;
import io.renren.modules.com.entity.ComContractImageEntity;
import io.renren.modules.com.entity.ComImageEntity;
import io.renren.modules.com.service.ComContractService;

@Service("comContractService")
public class ComContractServiceImpl implements ComContractService {
	@Autowired
	private ComContractDao comContractDao;

	@Autowired
	private ComAssetDao comAssetDao;
	@Autowired
	private ComContractAssetDao comContractAssetDao;
	@Autowired
	private ComContractImageDao comContractImageDao;
	@Autowired
	private ComImageDao comImageDao;

	@Override
	public ComContractEntity queryObject(Long contractId) {
		return this.queryObjectPrivate(contractId);
	}

	private ComContractEntity queryObjectPrivate(Long contractId) {
		ComContractEntity entity = comContractDao.queryObject(contractId);
		if (entity != null) {
			Map<String, Object> map = new HashMap<>();
			map.put("contractId", contractId);
			List<ComContractAssetEntity> asset = comContractAssetDao.queryList(map);
			if (CollectionUtil.isNotEmpty(asset)) {
				String assetIds = "";
				for (ComContractAssetEntity comContractAssetEntity : asset) {
					assetIds += comContractAssetEntity.getAssetId() + ";";
				}
				entity.setAssetIds(assetIds);
			}
			map.put("imageType", Constant.IMAGE_TYPE_CONTRACT);
			List<ComContractImageEntity> image = comContractImageDao.queryList(map);
			if (CollectionUtil.isNotEmpty(image)) {
				String imageIds = "";
				for (ComContractImageEntity comContractImageEntity : image) {
					imageIds += comContractImageEntity.getImageId() + ";";
				}
				entity.setImageIds(imageIds);
			}
		}
		return entity;
	}

	@Override
	public List<ComContractEntity> queryList(Map<String, Object> map) {
		return this.queryListPrivate(map);
	}

	private List<ComContractEntity> queryListPrivate(Map<String, Object> map) {
		String d1 = (String) map.get("endDateS");
		String d2 = (String) map.get("endDateE");
		if (!StrUtil.hasBlank(d1, d2)) {
			Date date1 = DateUtil.parse(d1);
			Date date2 = DateUtil.parse(d2);
			if (date1 != null && date2 != null && date1.after(date2)) {
				throw new RRException("开始日期不能大于结束日期");
			}
		}
		List<ComContractEntity> list = comContractDao.queryList(map);
		for (ComContractEntity comContractEntity : list) {
			map.put("contractId", comContractEntity.getContractId());
			List<ComContractAssetEntity> assetList = comContractAssetDao.queryList(map);
			String roomNum = "";
			for (ComContractAssetEntity comContractAssetEntity : assetList) {
				ComAssetEntity asset = comAssetDao.queryObject(comContractAssetEntity.getAssetId());
				if (asset != null) {
					roomNum += ";" + asset.getRoomNum();
				}
			}
			if (StringUtils.isNotBlank(roomNum)) {

				roomNum = roomNum.substring(1);
			}
			comContractEntity.setRoomNum(roomNum);
		}
		return list;
	}

	@Override
	public int queryTotal(Map<String, Object> map) {
		return comContractDao.queryTotal(map);
	}

	@Transactional
	public R save(ComContractEntity comContract) {
		return this.savePrivate(comContract);
	}

	private R savePrivate(ComContractEntity comContract) {
		ComContractEntity entity = comContractDao.queryByContractNo(comContract.getContractNo());
		if (entity != null) {
			return R.error("该合同号已存在");
		}
		int dateLong = DateUtils.dateBetween(comContract.getStartDate(), comContract.getEndDate());
		comContract.setDateLong(Double.valueOf(dateLong));
		R r = setLeasebackAndTotalPrice(comContract);
		if ((Integer) r.get("code") != 0) {
			return r;
		}
		// 计算租期
		comContractDao.save(comContract);
		r = this.saveAssetAndImage(comContract);
		return r;
	}

	@Transactional
	public R update(ComContractEntity comContract) {
		return this.updatePrivate(comContract);
	}

	private R updatePrivate(ComContractEntity comContract) {
		ComContractEntity entity = comContractDao.queryByContractNo(comContract.getContractNo());
		if (entity != null && entity.getContractId().longValue() != comContract.getContractId().longValue()) {
			return R.error("该合同号已存在");
		}
		int dateLong = DateUtils.dateBetween(comContract.getStartDate(), comContract.getEndDate());
		comContract.setDateLong(Double.valueOf(dateLong));
		R r = setLeasebackAndTotalPrice(comContract);
		if ((Integer) r.get("code") != 0) {
			return r;
		}
		ComContractImageEntity comContractImage = new ComContractImageEntity();
		comContractImage.setImageType(Constant.IMAGE_TYPE_CONTRACT);
		comContractImage.setContractId(comContract.getContractId());
		comContractImageDao.deleteAllImage(comContractImage);
		comContractDao.deleteAllAsset(comContract);
		r = this.saveAssetAndImage(comContract);
		if ((Integer) r.get("code") != 0) {
			return r;
		}
		comContractDao.update(comContract);
		return R.ok();
	}

	private R setLeasebackAndTotalPrice(ComContractEntity comContract) {
		// 计算总价
		String[] assetIds = StrUtil.split(comContract.getAssetIds(), ";");
		Double totalSquare = 0D;
		for (String assetId : assetIds) {
			if (StrUtil.isNotBlank(assetId)) {
				ComAssetEntity asset = comAssetDao.queryObject(Long.valueOf(assetId));
				if (asset == null) {
					return R.error("找不到所选房号信息");
				}
				ComContractAssetEntity entity = new ComContractAssetEntity();
				entity.setContractId(comContract.getContractId());
				entity.setAssetId(Long.valueOf(assetId));
				List<ComContractEntity> list = comContractDao.queryByAssetId(entity);
				if (CollectionUtil.isNotEmpty(list)) {
					for (ComContractEntity comContractEntity : list) {
						if (!(DateUtil.parse(comContract.getEndDate()).getTime() < DateUtil
								.parse(comContractEntity.getStartDate()).getTime()
								|| DateUtil.parse(comContract.getStartDate()).getTime() > DateUtil
										.parse(comContractEntity.getEndDate()).getTime())) {
							return R.error("所选房号已有有效的合同");
						}

					}
				}
				totalSquare = NumberUtil.add(totalSquare, asset.getRoomSquare());
			}
		}
		if (comContract.getTotalSquare() == null) {
			comContract.setTotalSquare(totalSquare);
		}
		// 返租信息
		if (StrUtil.equals(comContract.getLeasebackFlag(), "0")) {
			if (StrUtil.isBlank(comContract.getLeasebackContract()) || comContract.getUnitLprice() == null) {
				return R.error("返租合同或单价不可为空");
			}
			comContract.setTotalLprice(NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
					comContract.getUnitLprice()));
		} else {
			comContract.setTotalLprice(null);
			comContract.setUnitLprice(null);
			comContract.setLeasebackContract(null);
			comContract.setOwnerName(null);
		}

		if (comContract.getUnitEnergy() != null) {
			comContract.setTotalEnergy(NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
					comContract.getUnitEnergy()));
		} else {
			comContract.setTotalEnergy(null);
		}
		if (comContract.getUnitManage() != null) {
			comContract.setTotalManage(NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
					comContract.getUnitManage()));
		} else {
			comContract.setTotalManage(null);
		}
		if (comContract.getUnitOther() != null) {
			comContract.setTotalOther(NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
					comContract.getUnitOther()));
		} else {
			comContract.setTotalOther(null);
		}
		if (comContract.getUnitPrice() != null) {
			comContract.setTotalPrice(NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
					comContract.getUnitPrice()));
		} else {
			comContract.setTotalPrice(null);
		}
		if (comContract.getUnitProperty() != null) {
			comContract.setTotalProperty(NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
					comContract.getUnitProperty()));
		} else {
			comContract.setTotalProperty(null);
		}
		if (comContract.getUnitBond() != null) {
			comContract.setTotalBond(comContract.getUnitBond());
		} else {
			comContract.setTotalBond(null);
		}
		comContract.setTotalInter(comContract.getUnitInter() == null ? null : comContract.getUnitInter());
		comContract.setTotalStop(comContract.getUnitStop() == null ? null : comContract.getUnitStop());
		comContract.setTotalOther1(comContract.getUnitOther1() == null ? null : comContract.getUnitOther1());

		// 总金额
		comContract.setTotalMoney(
				NumberUtils.add(comContract.getTotalBond(), comContract.getTotalEnergy(), comContract.getTotalManage(),
						comContract.getTotalOther(), comContract.getTotalPrice(), comContract.getTotalProperty(),
						comContract.getTotalOther1(), comContract.getTotalStop(), comContract.getTotalInter()));
		if (Constant.NO.equals(comContract.getSuppleFlag())) {
			comContract.setSuppleBond(null);
			comContract.setSuppleEnergy(null);
			comContract.setSuppleManage(null);
			comContract.setSuppleOther(null);
			comContract.setSupplePrice(null);
			comContract.setSuppleProperty(null);
			comContract.setSuppleOther1(null);
			comContract.setSuppleInter(null);
			comContract.setSuppleStop(null);
			comContract.setEffectDate(null);
			comContract.setSuppleTotal(null);
		} else {
			comContract.setStotalBond(comContract.getSuppleBond() == null ? null : comContract.getSuppleBond());
			comContract.setStotalEnergy(comContract.getSuppleEnergy() == null ? null
					: NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
							comContract.getSuppleEnergy()));
			comContract.setStotalManage(comContract.getSuppleManage() == null ? null
					: NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
							comContract.getSuppleManage()));
			comContract.setStotalOther(comContract.getSuppleOther() == null ? null
					: NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
							comContract.getSuppleOther()));
			comContract.setStotalPrice(comContract.getSupplePrice() == null ? null
					: NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
							comContract.getSupplePrice()));
			comContract.setStotalProperty(comContract.getSuppleProperty() == null ? null
					: NumberUtils.mul(comContract.getTotalSquare(), comContract.getDateLong(),
							comContract.getSuppleProperty()));
			comContract.setStotalOther1(comContract.getSuppleOther1() == null ? null : comContract.getSuppleOther1());
			comContract.setStotalInter(comContract.getSuppleInter() == null ? null : comContract.getSuppleInter());
			comContract.setStotalStop(comContract.getSuppleStop() == null ? null : comContract.getSuppleStop());
			// comContract.setEffectDate(null);
			comContract.setSuppleTotal(NumberUtils.add(comContract.getStotalBond(), comContract.getStotalEnergy(),
					comContract.getStotalManage(), comContract.getStotalOther(), comContract.getStotalPrice(),
					comContract.getStotalProperty(), comContract.getStotalOther1(), comContract.getStotalStop(),
					comContract.getStotalInter()));
		}
		return R.ok();

	}

	private R saveAssetAndImage(ComContractEntity comContract) {
		// 保存房子资产
		String[] assetIds = StrUtil.split(comContract.getAssetIds(), ";");
		ComContractAssetEntity contractAssetEntity = null;
		// String assetIdsStr = "";
		// String buildIdsStr = "";
		for (String assetId : assetIds) {
			if (StrUtil.isNotBlank(assetId)) {
				ComAssetEntity asset = comAssetDao.queryObject(Long.valueOf(assetId));
				if (asset == null) {
					return R.error("找不到所选房号信息");
				}
				// SysBuildEntity build =
				// sysBuildDao.queryObject(asset.getBuildId());
				// if (build != null) {
				// buildIdsStr += ";" + build.getBuildName();
				// }
				// assetIdsStr += ";" + asset.getRoomNum();
				contractAssetEntity = new ComContractAssetEntity();
				contractAssetEntity.setAssetId(Long.valueOf(assetId));
				contractAssetEntity.setContractId(comContract.getContractId());
				comContractAssetDao.save(contractAssetEntity);
			}
		}

		// assetIdsStr = assetIdsStr.substring(1);
		// buildIdsStr = buildIdsStr.substring(1);
		// comContract.setBuildIds(buildIdsStr);
		// comContract.setAssetIds(assetIdsStr);
		// comContractDao.update(comContract);
		// 保存合同照片
		String[] imageIds = StrUtil.split(comContract.getImageIds(), ";");
		ComContractImageEntity contractImageEntity = null;
		for (String imageId : imageIds) {
			if (StrUtil.isNotBlank(imageId)) {
				ComImageEntity image = comImageDao.queryObject(Long.valueOf(imageId));
				if (image == null) {
					return R.error("找不到图片信息");
				}

				contractImageEntity = new ComContractImageEntity();
				contractImageEntity.setImageId(Long.valueOf(imageId));
				contractImageEntity.setContractId(comContract.getContractId());
				contractImageEntity.setImageType(Constant.IMAGE_TYPE_CONTRACT);
				comContractImageDao.save(contractImageEntity);
			}
		}
		return R.ok();
	}

	@Override
	public void delete(Long contractId) {
		comContractDao.delete(contractId);
	}

	@Override
	public void deleteBatch(Long[] contractIds) {
		comContractDao.deleteBatch(contractIds);
	}

	@Override
	public R updateSupple(ComContractEntity comContract) {
		return this.updateSupplePrivate(comContract);
	}

	private R updateSupplePrivate(ComContractEntity comContract) {
		ComContractEntity entity = comContractDao.queryObject(comContract.getContractId());
		if (entity == null) {
			return R.error("合同不存在");
		}
		entity.setSuppleBond(comContract.getSuppleBond());
		entity.setSuppleEnergy(comContract.getSuppleEnergy());
		entity.setSuppleManage(comContract.getSuppleManage());
		entity.setSuppleOther(comContract.getSuppleOther());
		entity.setSupplePrice(comContract.getSupplePrice());
		entity.setSuppleProperty(comContract.getSuppleProperty());
		entity.setSuppleFlag(Constant.YES);
		entity.setSuppleOther1(comContract.getSuppleOther1());
		entity.setSuppleInter(comContract.getSuppleInter());
		entity.setSuppleStop(comContract.getSuppleStop());
		entity.setEffectDate(comContract.getEffectDate());

		entity.setStotalBond(comContract.getSuppleBond() == null ? null : comContract.getSuppleBond());
		entity.setStotalEnergy(comContract.getSuppleEnergy() == null ? null
				: NumberUtils.mul(entity.getTotalSquare(), entity.getDateLong(), comContract.getSuppleEnergy()));
		entity.setStotalManage(comContract.getSuppleManage() == null ? null
				: NumberUtils.mul(entity.getTotalSquare(), entity.getDateLong(), comContract.getSuppleManage()));
		entity.setStotalOther(comContract.getSuppleOther() == null ? null
				: NumberUtils.mul(entity.getTotalSquare(), entity.getDateLong(), comContract.getSuppleOther()));
		entity.setStotalPrice(comContract.getSupplePrice() == null ? null
				: NumberUtils.mul(entity.getTotalSquare(), entity.getDateLong(), comContract.getSupplePrice()));
		entity.setStotalProperty(comContract.getSuppleProperty() == null ? null
				: NumberUtils.mul(entity.getTotalSquare(), entity.getDateLong(), comContract.getSuppleProperty()));
		entity.setStotalOther1(comContract.getSuppleOther1() == null ? null : comContract.getSuppleOther1());
		entity.setStotalInter(comContract.getSuppleInter() == null ? null : comContract.getSuppleInter());
		entity.setStotalStop(comContract.getSuppleStop() == null ? null : comContract.getSuppleStop());
		// comContract.setEffectDate(null);
		entity.setSuppleTotal(NumberUtils.add(entity.getStotalBond(), entity.getStotalEnergy(),
				entity.getStotalManage(), entity.getStotalOther(), entity.getStotalPrice(), entity.getStotalProperty(),
				entity.getStotalOther1(), entity.getStotalStop(), entity.getStotalInter()));
		comContractDao.update(entity);
		return R.ok();
	}

	@Transactional
	public R endContract(ComContractEntity comContract) {
		return this.endContractPrivate(comContract);
	}

	private R endContractPrivate(ComContractEntity comContract) {
		ComContractEntity entity = comContractDao.queryObject(comContract.getContractId());
		if (entity == null) {
			return R.error("合同不存在");
		}
		entity.setEndDate(comContract.getEndDate());
		entity.setEndReason(comContract.getEndReason());
		comContractDao.update(entity);
		String[] imageIds = StrUtil.split(comContract.getImageIds1(), ";");
		ComContractImageEntity contractImageEntity = null;
		for (String imageId : imageIds) {
			if (StrUtil.isNotBlank(imageId)) {
				ComImageEntity image = comImageDao.queryObject(Long.valueOf(imageId));
				if (image == null) {
					return R.error("找不到图片信息");
				}

				contractImageEntity = new ComContractImageEntity();
				contractImageEntity.setImageId(Long.valueOf(imageId));
				contractImageEntity.setContractId(comContract.getContractId());
				contractImageEntity.setImageType(Constant.IMAGE_TYPE_CONTRACT_END);
				comContractImageDao.save(contractImageEntity);
			}
		}
		return R.ok();
	}

	@Override
	public void contractExport(String title, List<ComContractEntity> contractList, OutputStream out) {
		this.contractExportPrivate(title, contractList, out);
	}

	@SuppressWarnings("deprecation")
	private void contractExportPrivate(String title, List<ComContractEntity> contractList, OutputStream out) {
		// 声明一个工作薄

		HSSFWorkbook workbook = new HSSFWorkbook();

		// 生成一个表格

		HSSFSheet sheet = workbook.createSheet(title);

		// 设置表格默认列宽度为15个字节

		sheet.setDefaultColumnWidth((short) 15);

		// 生成一个样式

		HSSFCellStyle style = workbook.createCellStyle();
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		//
		// 设置这些样式
		//

		// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
		style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
		style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
		style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		//
		// 生成一个字体
		//
		HSSFFont font = workbook.createFont();

		font.setFontHeightInPoints((short) 12);

		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		//
		// 把字体应用到当前的样式
		//
		style.setFont(font);

		HSSFRow row = null;
		row = sheet.createRow(0);
		HSSFCell cell = null;
		List<String> list = CollectionUtil.newArrayList("合同号", "房号", "租赁人", "合同开始日期", "合同截止日期", "签订人", "签订日期", "租期（月）",
				"面积(㎡)");
		for (int i = 0; i < 9; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(list.get(i));

			CellRangeAddress cra = new CellRangeAddress(0, 1, i, i);
			RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
			RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
			RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
			RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
			sheet.addMergedRegion(cra);
		}
		CellRangeAddress cra = new CellRangeAddress(0, 0, 9, 17);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(9);
		cell.setCellStyle(style);
		cell.setCellValue("单价");
		cra = new CellRangeAddress(0, 0, 18, 26);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(18);
		cell.setCellStyle(style);
		cell.setCellValue("含税总金额");
		cra = new CellRangeAddress(0, 1, 27, 27);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(27);
		cell.setCellStyle(style);
		cell.setCellValue("总计");
		cra = new CellRangeAddress(0, 0, 28, 29);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(28);
		cell.setCellStyle(style);
		cell.setCellValue("返租金额");
		cra = new CellRangeAddress(0, 1, 30, 30);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(30);
		cell.setCellStyle(style);
		cell.setCellValue("返租合同号");
		cra = new CellRangeAddress(0, 0, 31, 39);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(31);
		cell.setCellStyle(style);
		cell.setCellValue("变更单价");
		cra = new CellRangeAddress(0, 0, 40, 48);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(40);
		cell.setCellStyle(style);
		cell.setCellValue("变更后价格");
		cra = new CellRangeAddress(0, 1, 49, 49);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(49);
		cell.setCellStyle(style);
		cell.setCellValue("总计");
		cra = new CellRangeAddress(0, 1, 50, 50);
		RegionUtil.setBorderBottom(2, cra, sheet, workbook); // 下边框
		RegionUtil.setBorderLeft(2, cra, sheet, workbook); // 左边框
		RegionUtil.setBorderRight(2, cra, sheet, workbook); // 有边框
		RegionUtil.setBorderTop(2, cra, sheet, workbook); // 上边框
		sheet.addMergedRegion(cra);
		cell = row.createCell(50);
		cell.setCellStyle(style);
		cell.setCellValue("生效日期");

		row = sheet.createRow(1);
		for (int i = 0; i < 9; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
		}
		cell = row.createCell(27);
		cell.setCellStyle(style);
		cell = row.createCell(30);
		cell.setCellStyle(style);
		cell = row.createCell(49);
		cell.setCellStyle(style);
		cell = row.createCell(50);
		cell.setCellStyle(style);
		List<String> list2 = CollectionUtil.newArrayList("租金", "管理费", "物业费", "公共能耗费", "保证金", "居间服务费", "停车费", "租场费",
				"其他");
		for (int i = 9; i < 18; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(list2.get(i - 9));
		}
		for (int i = 18; i < 27; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(list2.get(i - 18));
		}
		cell = row.createCell(28);
		cell.setCellStyle(style);
		cell.setCellValue("单价租金");
		cell = row.createCell(29);
		cell.setCellStyle(style);
		cell.setCellValue("总租金");
		for (int i = 31; i < 40; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(list2.get(i - 31));
		}
		for (int i = 40; i < 49; i++) {
			cell = row.createCell(i);
			cell.setCellStyle(style);
			cell.setCellValue(list2.get(i - 40));
		}
		// cell = row.createCell(49);
		// cell.setCellStyle(style);
		// cell.setCellValue("总计");
		// cell = row.createCell(50);
		// cell.setCellStyle(style);
		// cell.setCellValue("生效日期");

		int count = 1;
		int i = 0;
		for (ComContractEntity contract : contractList) {
			count++;
			row = sheet.createRow(count);
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getContractNo());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getRoomNum());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getRentUser());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStartDate());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getEndDate());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSalesMan());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSalesDate());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getDateLong());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalSquare());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitPrice() == null ? "" : contract.getUnitPrice().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitManage() == null ? "" : contract.getUnitManage().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitProperty() == null ? "" : contract.getUnitProperty().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitEnergy() == null ? "" : contract.getUnitEnergy().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitBond() == null ? "" : contract.getUnitBond().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitInter() == null ? "" : contract.getUnitInter().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitStop() == null ? "" : contract.getUnitStop().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitOther1() == null ? "" : contract.getUnitOther1().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitOther() == null ? "" : contract.getUnitOther().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalPrice() == null ? "" : contract.getTotalPrice().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalManage() == null ? "" : contract.getTotalManage().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalProperty() == null ? "" : contract.getTotalProperty().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalEnergy() == null ? "" : contract.getTotalEnergy().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalBond() == null ? "" : contract.getTotalBond().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalInter() == null ? "" : contract.getTotalInter().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalStop() == null ? "" : contract.getTotalStop().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalOther1() == null ? "" : contract.getTotalOther1().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalOther() == null ? "" : contract.getTotalOther().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalMoney() == null ? "" : contract.getTotalMoney().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getUnitLprice() == null ? "" : contract.getUnitLprice().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getTotalLprice() == null ? "" : contract.getTotalLprice().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getLeasebackContract());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSupplePrice() == null ? "" : contract.getSupplePrice().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleManage() == null ? "" : contract.getSuppleManage().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleProperty() == null ? "" : contract.getSuppleProperty().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleEnergy() == null ? "" : contract.getSuppleEnergy().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleBond() == null ? "" : contract.getSuppleBond().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleInter() == null ? "" : contract.getSuppleInter().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleStop() == null ? "" : contract.getSuppleStop().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleOther1() == null ? "" : contract.getSuppleOther1().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleOther() == null ? "" : contract.getSuppleOther().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalPrice() == null ? "" : contract.getStotalPrice().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalManage() == null ? "" : contract.getStotalManage().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalProperty() == null ? "" : contract.getStotalProperty().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalEnergy() == null ? "" : contract.getStotalEnergy().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalBond() == null ? "" : contract.getStotalBond().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalInter() == null ? "" : contract.getStotalInter().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalStop() == null ? "" : contract.getStotalStop().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalOther1() == null ? "" : contract.getStotalOther1().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getStotalOther() == null ? "" : contract.getStotalOther().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getSuppleTotal() == null ? "" : contract.getSuppleTotal().toString());
			i++;
			cell = row.createCell(i);
			cell.setCellStyle(style2);
			cell.setCellValue(contract.getEffectDate());
			i = 0;
		}
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}
